In the Business Intelligence (BI) analysis domain, various data is presented to users in a form of a worksheet, such as a dimensional cross tabulation (crosstab) or a chart. Business analysts needs to be able to manipulate a dimensional crosstab and chart in a variety of ways. Next to seeing the actual values in the crosstab or chart, it is important for the analysts to get feedback on what filter criteria are applied to the displayed data. It is desirable for analysts to be able to get a fast overview of the data shown in a worksheet.
Individual filters applied to a dimensional crosstab consist in most cases of one single dimension category, which is called a “member” in Multi Dimensional Expression (MDX). For example, a crosstab can consist of products in the rows, years in the columns and revenue as measure. The values shown for the measure can be filtered using a single category (e.g. region “USA”), using an expression (e.g. where the product values for 1996 is more than 500M) or of a list of selected categories (e.g. a list or order methods: E-mail, Telephone, Mail). The filtering of the values in the crosstab can also be based on a combination of all these types of filters. For a user, it is desirable to easily view, create, update and delete these filter criteria.
In a dimensional crosstab, users can have multiple data sets on the rows and/or columns. A data set is a collection of categories from one dimension. On an edge (axis), it is possible to have a combination of multiple data sets. The data sets on such an edge (axis) can come from different dimensions, but also from the same dimension. In the crosstab itself, it is often difficult to see what the structure of the data is. When the crosstab is large, users may have to scroll or navigate to see what is actually in the crosstab. It is desirable for users to easily see what the crosstab structure is, and to be able to manipulate and navigate the data in a small screen real estate solution.
Some existing BI tools have a user interface that provides a dimension line or menu where all the dimensions available in the data source are shown. In this type of dimension line, every item is shown at the highest level in the dimension. If a user wants to filter the values on an item (e.g., priority), the user selects the item “Priority” in the dimension line to open a Priority menu and selects a desired category in the Priority menu. The selection of a category changes the values in the crosstab. In the menu, the selected filter is shown highlighted or in bold. This type of menu provides limited options and it is not possible for the users to manipulate the filter criteria further. Showing all dimensions consumes screen real-estate, and it is confusing to the users and makes it difficult to locate a desired dimension. The type of filter that can be shown in the menu is also limited to a single category within a dimension.
Another existing prior art shows the data structure near the rows for the row dimensions and near the columns for the column dimensions, and the overall filters in a separate container above or below the crosstab. This approach needs a relatively large screen real estate, and there is no coherence between these elements.
It is therefore desirable to provide a mechanism to provide information of crosstab in an efficient manner using a relatively small real estate.